# -*- coding: UTF-8 -*-
# http://www.sqlalchemy.org/docs/04/ormtutorial.html


from sqlalchemy import *
from sqlalchemy.orm import *

class User(object):
    def __init__(self, user, password):
        self.user = user
        self.password = password #MD5, SHA1 vms ofcoors
        #self.reset_password_secret = random()
        '''
        >>> import md5
        >>> m = md5.new()
        >>> m.update("lammas")
        >>> m.digest()
        '\x05T \xd8\xae\x0b\xf3\xc1\xd3\xd1\xdd\xac$}Xs'
        >>> m.hexdigest()
        '055420d8ae0bf3c1d3d1ddac247d5873'
        '''
    def __repr__(self):
        return "<User('%s','%s')>" % (self.user,self.password)

class Download(object):
    def __init__(self, *args):
        self.user = args[0]
        self.url = None
        self.done = None
        self.pid = None

        if len(args)>=3:
            self.url = args[1]
            self.done = args[2]
            self.pid = args[3]

    def __repr__(self):
        return "<Download('%s','%s','%s','%s','s')>" % (self.user,self.url,self.done,self.pid)

class OfflineMessage(object):
    def __init__(self, from_user, to_user, msg):
        self.from_user = from_user
        self.to_user = to_user
        self.msg = msg
        self.sent = False

    def __repr__(self):
         return "<OfflineMessage('%s','%s','%s')>" % (self.from_user,self.to_user,self.sent)

#class CommandsHistory ?

#http://www.sqlalchemy.org/docs/04/ormtutorial.html
class GBotDB:
    def __init__(self):
        #DB peab enne tehtud olema
        self.engine = create_engine('mysql://root@localhost/gbot', echo=True)
        self.metadata = MetaData()
        #Teeb ise tabelid, vist peaks
        #XXX Tabelid globaliks, kas garbage collector jätab nad rahule?
        self.users_table = Table('users', self.metadata,
            Column('id', Integer, primary_key=True),
            Column('user', String(50),nullable=False),
            Column('password', String(50))
        )

        self.downloads_table = Table('downloads', self.metadata,
            Column('id', Integer, primary_key=True),
            Column('user', Integer, ForeignKey('users.id')),
            Column('url', String(1024)),
            Column('done',Integer),
            Column('pid',Integer)
        )
        # Või teha et juuser peab joinima ja siis users.id'ga siduda ? :P ah, poh
        self.offlinemsgs_table = Table('offlinemessages', self.metadata,
            Column('id', Integer, primary_key=True),
            Column('from_user', String(128)),
            Column('to_url', String(128)),
            Column('msg', String(1024)),
            Column('sent',Boolean)
        )

        self.metadata.create_all(self.engine) #Create all tables?

        mapper(User, self.users_table)
        mapper(Download, self.downloads_table)
        mapper(OfflineMessage, self.offlinemsgs_table)
        
        self.Session = sessionmaker(bind=self.engine, autoflush=True, transactional=True)
        self.session = self.Session()

    def get_users(self):
        return self.session.query(User)

    def get_user(self, jid):
        return self.session.query(User).filter("user=:jid").params(jid=jid).all()

    def get_downloads(self):
        return self.session.query(Download)

    def get_offline_msgs(self,to_user):
        return self.session.query(OfflineMessage).filter("to_user=:user").params(user=to_user).all()
        #.filter -> .from_statement("SELECT * FROM users where name=:name").params(name='ed').all()
    
    def delete(self,entry):
        self.session.delete(entry)
        
    def set_offline_msg(self,from_user, to_user, msg):
        msg = OfflineMessage(from_user, to_user, msg)
        self.session.save(msg)

gb = GBotDB()
user = User('example@gmail.com','lammas')
gb.session.save(user)
gb.session.commit() # Force write to DB so we can get the id

print "User id:",user.id
dl = Download(user.id,'http://www.example.com',0,12345)
gb.session.save(dl)

for u in gb.get_users():
        print u.id,u.user

print "Jid:", gb.get_user("example@gmail.com")
